-- **************************************************************************
--    Project Name:   全程时效-虚假发货监控汇总表
--    Job Name:       jms_dm.dm_sham_send_monitor_sum_dt
--    Author :        季修魁
--    date：          2023/09/12
-- **************************************************************************
-- **************************************************************************

--------------汇总结果

insert overwrite table jms_dm.dm_sham_send_monitor_sum_dt partition (dt)
select to_date(taking_time)  as taking_time
,network_code
,network_name
,network_type
,agent_code
,agent_name
,sum(if(fst_duty_type is not null,1,0))                  as  total_cnt
,sum(if(fst_duty_type='始发网点停留超时',1,0))           as  start_code_stay_ovtime_cnt
,sum(if(fst_duty_type='始发网点-始发中心超时',1,0))      as  start_code_center_ovtime_cnt
,sum(if(fst_duty_type='始发中心停留超时',1,0))           as  first_center_ovtime_cnt
,sum(if(fst_duty_type='始发中心-中转机构',1,0))          as  first_center_transit_ovtime_cnt
,sum(if(fst_duty_type='中转机构停留超时',1,0))           as  transit_center_stay_ovtime_cnt
,sum(if(fst_duty_type='中转环节流转超时',1,0))           as  transit_center_run_ovtime_cnt
,sum(if(fst_duty_type='末端中心超时',1,0))               as  end_center_cnt
,sum(if(fst_duty_type='末端环节流转超时',1,0))           as  end_center_ovtime_cnt
,sum(if(fst_duty_type='末端网点停留超时',1,0))           as  end_code_stay_ovtime_cnt
,ordersource_code
,ordersource_name
,dt
from jms_dm.dm_sham_send_monitor_detail_dt_new
where dt>=date_sub('{{ execution_date | cst_ds }}',32) and dt<='{{ execution_date | cst_ds }}'
 group by to_date(taking_time)
,network_code
,network_name
,network_type
,agent_code
,agent_name
,ordersource_code
,ordersource_name
,dt
distribute by dt
;
